//
//  NoteController.swift
//  hello
//
//  Created by 赵康 on 2025/3/22.
//

import Fluent
import FluentSQL
import Queues
import Redis
import Vapor

struct NoteController: RouteCollection {
  func boot(routes: any RoutesBuilder) throws {
    let notes = routes.grouped("\(RouteEnum.notes.rawValue)")
    let protected = notes.grouped(AuthMiddleware())

    protected
      .post(
        "\(RouteEnum.createMyNoteUnderSpecificAudio.rawValue)",
        use: createMyNoteUnderSpecificAudio
      )
    protected
      .get(
        "\(RouteEnum.fetchMyNotesUnderSpecificAudio.rawValue)",
        ":audioID",
        use: fetchMyNotesUnderSpecificAudio
      )
    protected
      .put("\(RouteEnum.updateMyNote.rawValue)", ":id", use: updateMyNote)

    protected.delete("\(RouteEnum.deleteMyNote.rawValue)", ":id", use: deleteMyNote)

    protected
      .get(
        "\(RouteEnum.fetchMyNotesUnderSpecificTag.rawValue)",
        ":tag",
        use: fetchMyNotesUnderSpecificTag
      )

    protected.get("\(RouteEnum.search.rawValue)", use: search)

    protected.get(
      "\(RouteEnum.fetchAllUsersNotesUnderSpecificAudio.rawValue)", ":audioID",
      use: fetchAllUsersNotesUnderSpecificAudio)

    protected.get("\(RouteEnum.fetchAllMyNotes.rawValue)", use: fetchAllMyNotes)

    protected.post("\(RouteEnum.toggleNoteLike.rawValue)", ":id", use: toggleNoteLike)

    protected.post("\(RouteEnum.reportNote.rawValue)", use: reportNote)

    protected.post("\(RouteEnum.createNoteTag.rawValue)", use: createNoteTag)
    protected.get("\(RouteEnum.fetchMyNoteTags.rawValue)", use: fetchMyNoteTags)
    protected.put("\(RouteEnum.updateNoteTag.rawValue)", ":id", use: updateNoteTag)
    protected.delete("\(RouteEnum.deleteNoteTag.rawValue)", ":id", use: deleteNoteTag)
  }

  // 针对特定音频创建笔记
  @Sendable
  func createMyNoteUnderSpecificAudio(req: Request) async throws -> Note.Response {
    let userID = try req.authenticatedUserID
    let createRequest = try req.content.decode(Note.Create.self)

    // 验证音频是否存在
    guard
      let audio = try await Audio.query(on: req.db)
        .filter(\.$id == createRequest.audioID)
        .with(\.$album)
        .first()
    else {
      throw Abort(.notFound, reason: "音频不存在")
    }

    // 获取用户信息
    guard let user = try await User.find(userID, on: req.db) else {
      throw Abort(.notFound, reason: "用户不存在")
    }

    // 创建笔记
    let note = Note(
      content: createRequest.content,
      timestamp: createRequest.timestamp ?? 0.0,
      tags: createRequest.tags ?? [],
      userID: userID,
      audioID: createRequest.audioID,
      isPublic: createRequest.isPublic ?? true,
      likedBy: [],
      likeCount: 0,
      reviewStatus: .none,
      answer: createRequest.answer,  // 新增
      isArabic: createRequest.isArabic ?? true,  // 新增，默认为阿拉伯语
      isAnswerArabic: createRequest.isAnswerArabic ?? false,  // 新增，默认不是阿拉伯语
      createdAt: Date(),
      updatedAt: Date()
    )

    // 新增：设置初始复习状态
    note.reviewStatus = ReviewStatus.none

    try await note.save(on: req.db)

    // 新增：如果启用了自动添加到复习计划，创建复习记录
    if createRequest.addToReview == true {
      // 创建符合Anki算法的复习记录
      let review = NoteReview(
        noteID: note.id!,
        userID: userID,
        difficulty: .good,  // 默认为"良好"难度
        nextReviewDate: Date(),  // 设置为当前时间，使其立即可复习
        reviewCount: 0,
        easeFactor: 2.5,  // 初始难度因子
        interval: 0,  // 初始间隔
        learningStage: .new,  // 新卡片
        lapseCount: 0
      )
      try await review.save(on: req.db)

      // 更新笔记状态
      note.reviewStatus = ReviewStatus.scheduled
      try await note.save(on: req.db)

      // 清除今日复习列表缓存
      let todayReviewsCacheKey = CacheConfig.KeyBuilder.todayReviews(userID: userID)
      try await req.cache.delete(forKey: todayReviewsCacheKey, on: req.redis)

      // 清除统计缓存
      let statsKey = CacheConfig.KeyBuilder.reviewStats(userID: userID)
      try await req.cache.delete(forKey: statsKey, on: req.redis)
    }

    // 清除相关缓存
    var keysToDelete = [RedisKey]()

    // 1. 添加用户在特定音频下的笔记列表缓存键
    keysToDelete.append(
      CacheConfig.KeyBuilder.notesByAudio(audioID: createRequest.audioID, userID: userID))

    // 2. 添加所有用户在特定音频下的笔记列表缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.allNotesByAudio(audioID: createRequest.audioID))

    // 3. 添加用户所有笔记的缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.notesByUser(userID: userID))

    // 添加这一行到 keysToDelete 列表中
    keysToDelete.append(CacheConfig.KeyBuilder.noteTagsByUser(userID: userID))

    // 4. 如果笔记包含标签，添加标签相关缓存键
    if let tags = createRequest.tags, !tags.isEmpty {
      for tag in tags {
        keysToDelete.append(CacheConfig.KeyBuilder.notesByTag(tag: tag, userID: userID))
      }
    }

    // 批量删除所有缓存
    if !keysToDelete.isEmpty {
      let _ = req.redis.delete(keysToDelete)
    }

    return Note.Response(
      id: note.id,
      content: note.content,
      timestamp: note.timestamp,
      tags: note.tags,
      userID: userID,
      audioID: createRequest.audioID,
      isPublic: createRequest.isPublic ?? true,

      likeCount: 0,
      isLiked: false,

      audioTitle: audio.title,
      audioCover: audio.cover,
      albumCover: audio.album.cover,
      userName: user.name,
      userAvatar: user.avatar,

      reviewStatus: note.reviewStatus,

      answer: note.answer,  // 新增
      isArabic: note.isArabic,  // 新增
      isAnswerArabic: note.isAnswerArabic,  // 新增

      createdAt: note.createdAt,
      updatedAt: note.updatedAt
    )
  }

  // 获取我在某一个音频下的所有笔记
  @Sendable
  func fetchMyNotesUnderSpecificAudio(req: Request) async throws -> [Note.Response] {
    let userID = try req.authenticatedUserID
    guard let audioID = req.parameters.get("audioID", as: UUID.self) else {
      throw Abort(.badRequest, reason: "无效的音频ID")
    }

    // 尝试从缓存获取
    let cacheKey = CacheConfig.KeyBuilder.notesByAudio(audioID: audioID, userID: userID)
    if let cachedNotes = try await req.cache.get(
      forKey: cacheKey,
      as: [Note.Response].self,
      from: req.redis
    ) {
      return cachedNotes
    }

    // 查询数据库
    let notes = try await Note.query(on: req.db)
      .filter(\.$user.$id == userID)
      .filter(\.$audio.$id == audioID)
      .sort(\.$createdAt, .descending)
      .with(\.$audio) { audio in
        audio.with(\.$album)
      }
      .with(\.$user)
      .all()

    // 构建响应
    let responses = notes.map { note -> Note.Response in
      return Note.Response(
        id: note.id,
        content: note.content,
        timestamp: note.timestamp ?? 0,
        tags: note.tags ?? [],
        userID: userID,
        audioID: audioID,
        isPublic: note.isPublic,

        likeCount: note.likeCount,
        isLiked: note.likedBy?.contains(userID) ?? false,

        audioTitle: note.audio.title,
        audioCover: note.audio.cover,
        albumCover: note.audio.album.cover,
        userName: note.user.name,
        userAvatar: note.user.avatar,

        reviewStatus: note.reviewStatus,

        answer: note.answer,
        isArabic: note.isArabic,
        isAnswerArabic: note.isAnswerArabic,

        createdAt: note.createdAt,
        updatedAt: note.updatedAt
      )
    }

    // 缓存响应
    try await req.cache.set(
      responses,
      forKey: cacheKey,
      duration: CacheConfig.Duration.medium,
      type: .note,
      on: req.redis
    )

    return responses
  }

  // 获取某一个音频下的所有用户笔记
  @Sendable
  func fetchAllUsersNotesUnderSpecificAudio(req: Request) async throws -> [Note.Response] {
    // 获取当前认证用户ID（用于权限检查或区分笔记）
    let currentUserID = try req.authenticatedUserID

    // 从URL参数获取音频ID
    guard let audioID = req.parameters.get("audioID", as: UUID.self) else {
      throw Abort(.badRequest, reason: "无效的音频ID")
    }

    // 尝试从缓存获取
    let cacheKey = CacheConfig.KeyBuilder.allNotesByAudio(audioID: audioID)
    if let cachedNotes = try await req.cache.get(
      forKey: cacheKey,
      as: [Note.Response].self,
      from: req.redis
    ) {
      return cachedNotes
    }

    // 查询数据库，获取指定音频的所有笔记
    let notes = try await Note.query(on: req.db)
      .filter(\.$audio.$id == audioID)
      .filter(\.$isPublic == true)
      .sort(\.$createdAt, .descending)
      .with(\.$audio) { audio in
        audio.with(\.$album)
      }
      .with(\.$user)
      .all()

    // 构建响应
    let responses = notes.map { note -> Note.Response in
      return Note.Response(
        id: note.id,
        content: note.content,
        timestamp: note.timestamp ?? 0,
        tags: note.tags ?? [],
        userID: note.$user.id,
        audioID: audioID,
        isPublic: note.isPublic,

        likeCount: note.likeCount,
        isLiked: note.likedBy?.contains(currentUserID) ?? false,

        audioTitle: note.audio.title,
        audioCover: note.audio.cover,
        albumCover: note.audio.album.cover,
        userName: note.user.name,
        userAvatar: note.user.avatar,

        reviewStatus: note.reviewStatus,

        answer: note.answer,
        isArabic: note.isArabic,
        isAnswerArabic: note.isAnswerArabic,

        createdAt: note.createdAt,
        updatedAt: note.updatedAt
      )
    }

    // 缓存响应
    try await req.cache.set(
      responses,
      forKey: cacheKey,
      duration: CacheConfig.Duration.short,  // 公共数据缓存时间较短
      type: .note,
      on: req.redis
    )

    return responses
  }

  // 获取我的所有笔记
  @Sendable
  func fetchAllMyNotes(req: Request) async throws -> [Note.Response] {
    let userID = try req.authenticatedUserID

    // 尝试从缓存获取
    let cacheKey = CacheConfig.KeyBuilder.notesByUser(userID: userID)
    if let cachedNotes = try await req.cache.get(
      forKey: cacheKey,
      as: [Note.Response].self,
      from: req.redis
    ) {
      return cachedNotes
    }

    // 查询数据库
    let notes = try await Note.query(on: req.db)
      .filter(\.$user.$id == userID)
      .sort(\.$createdAt, .descending)
      .with(\.$audio) { audio in
        audio.with(\.$album)
      }
      .with(\.$user)
      .all()

    // 构建响应
    let responses = notes.map { note -> Note.Response in
      return Note.Response(
        id: note.id,
        content: note.content,
        timestamp: note.timestamp ?? 0,
        tags: note.tags ?? [],
        userID: note.$user.id,
        audioID: note.$audio.id,
        isPublic: note.isPublic,

        likeCount: note.likeCount,
        isLiked: note.likedBy?.contains(userID) ?? false,

        audioTitle: note.audio.title,
        audioCover: note.audio.cover,
        albumCover: note.audio.album.cover,
        userName: note.user.name,
        userAvatar: note.user.avatar,

        reviewStatus: note.reviewStatus,

        answer: note.answer,
        isArabic: note.isArabic,
        isAnswerArabic: note.isAnswerArabic,

        createdAt: note.createdAt,
        updatedAt: note.updatedAt
      )
    }

    // 缓存响应
    try await req.cache.set(
      responses,
      forKey: cacheKey,
      duration: CacheConfig.Duration.short,
      type: .note,
      on: req.redis
    )

    return responses
  }

  // 获取我的某一个标签下的所有笔记
  @Sendable
  func fetchMyNotesUnderSpecificTag(req: Request) async throws -> [Note.Response] {
    let userID = try req.authenticatedUserID
    guard let tag = req.parameters.get("tag") else {
      throw Abort(.badRequest, reason: "无效的标签")
    }

    // 构建缓存键
    let cacheKey = CacheConfig.KeyBuilder.notesByTag(tag: tag, userID: userID)

    // 尝试从缓存获取
    if let cachedNotes = try await req.cache.get(
      forKey: cacheKey,
      as: [Note.Response].self,
      from: req.redis
    ) {
      return cachedNotes
    }

    // 修改查询以加载关联数据
    let notes = try await Note.query(on: req.db)
      .filter(\.$user.$id == userID)
      .with(\.$audio) { audio in
        audio.with(\.$album)
      }
      .with(\.$user)
      .all()

    // 在内存中过滤包含指定标签的笔记
    let filteredNotes = notes.filter { note in
      // 安全地处理可选的 tags 数组
      guard let tags = note.tags else {
        return false  // 如果 tags 为 nil，则不匹配
      }
      return tags.contains(tag)
    }

    // 构建响应
    let responses = filteredNotes.map { note -> Note.Response in
      return Note.Response(
        id: note.id,
        content: note.content,
        timestamp: note.timestamp ?? 0,  // 提供默认值
        tags: note.tags ?? [],  // 提供默认空数组
        userID: userID,
        audioID: note.$audio.id,
        isPublic: note.isPublic,

        // 点赞信息
        likeCount: note.likeCount,
        isLiked: note.likedBy?.contains(userID) ?? false,

        audioTitle: note.audio.title,
        audioCover: note.audio.cover,
        albumCover: note.audio.album.cover,
        userName: note.user.name,
        userAvatar: note.user.avatar,

        reviewStatus: note.reviewStatus,

        answer: note.answer,
        isArabic: note.isArabic,
        isAnswerArabic: note.isAnswerArabic,

        createdAt: note.createdAt,
        updatedAt: note.updatedAt
      )
    }

    // 缓存响应
    try await req.cache.set(
      responses,
      forKey: cacheKey,
      duration: CacheConfig.Duration.medium,
      type: .note,
      on: req.redis
    )

    return responses
  }

  // 更新笔记
  @Sendable
  func updateMyNote(req: Request) async throws -> Note.Response {
    let userID = try req.authenticatedUserID
    guard let noteID = req.parameters.get("id", as: UUID.self) else {
      throw Abort(.badRequest, reason: "无效的笔记ID")
    }

    let updateRequest = try req.content.decode(Note.Update.self)

    // 1. 查询并更新笔记
    guard let note = try await Note.find(noteID, on: req.db) else {
      throw Abort(.notFound, reason: "笔记不存在")
    }

    // 验证用户权限
    guard note.$user.id == userID else {
      throw Abort(.forbidden, reason: "无权限修改此笔记")
    }

    // 保存原始标签用于缓存清理
    let originalTags = note.tags ?? []

    // 更新笔记内容
    note.content = updateRequest.content
    if let timestamp = updateRequest.timestamp {
      note.timestamp = timestamp
    }
    if let tags = updateRequest.tags {
      note.tags = tags
    }
    if let isPublic = updateRequest.isPublic {
      note.isPublic = isPublic
    }

    // 新增字段更新
    if let answer = updateRequest.answer {
      note.answer = answer
    }
    if let isArabic = updateRequest.isArabic {
      note.isArabic = isArabic
    }
    if let isAnswerArabic = updateRequest.isAnswerArabic {
      note.isAnswerArabic = isAnswerArabic
    }

    note.updatedAt = Date()
    try await note.save(on: req.db)

    // 2. 单独加载关联数据用于响应
    let audio = try await Audio.query(on: req.db)
      .filter(\.$id == note.$audio.id)
      .with(\.$album)
      .first()!

    let user = try await User.find(userID, on: req.db)!

    // 清除所有相关缓存
    var keysToDelete = [RedisKey]()

    // 1. 添加音频相关缓存键
    keysToDelete.append(
      CacheConfig.KeyBuilder.notesByAudio(audioID: note.$audio.id, userID: userID))

    // 2. 添加用户所有笔记的缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.notesByUser(userID: userID))

    // 3. 添加音频下所有用户笔记的缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.allNotesByAudio(audioID: note.$audio.id))

    keysToDelete.append(CacheConfig.KeyBuilder.noteTagsByUser(userID: userID))

    // 4. 如果笔记有标签，添加标签相关缓存键
    // 清除原始标签的缓存
    for tag in originalTags {
      keysToDelete.append(CacheConfig.KeyBuilder.notesByTag(tag: tag, userID: userID))
    }

    // 清除新标签的缓存
    if let newTags = note.tags {
      for tag in newTags {
        // 避免重复添加相同的标签缓存键
        let tagKey = CacheConfig.KeyBuilder.notesByTag(tag: tag, userID: userID)
        if !keysToDelete.contains(tagKey) {
          keysToDelete.append(tagKey)
        }
      }
    }

    // 批量删除所有缓存
    if !keysToDelete.isEmpty {
      let _ = req.redis.delete(keysToDelete)
    }

    // 构建响应
    return Note.Response(
      id: note.id,
      content: note.content,
      timestamp: note.timestamp ?? 0,
      tags: note.tags ?? [],
      userID: userID,
      audioID: note.$audio.id,
      isPublic: note.isPublic,

      likeCount: note.likeCount,
      isLiked: note.likedBy?.contains(userID) ?? false,

      audioTitle: audio.title,  // 使用预加载的 audio 变量
      audioCover: audio.cover,  // 使用预加载的 audio 变量
      albumCover: audio.album.cover,  // 使用预加载的 audio 变量
      userName: user.name,  // 使用预加载的 user 变量
      userAvatar: user.avatar,  // 使用预加载的 user 变量

      reviewStatus: note.reviewStatus,

      answer: note.answer,
      isArabic: note.isArabic,
      isAnswerArabic: note.isAnswerArabic,

      createdAt: note.createdAt,
      updatedAt: note.updatedAt
    )
  }

  // 删除笔记
  @Sendable
  func deleteMyNote(req: Request) async throws -> BasicResponse {
    let userID = try req.authenticatedUserID
    guard let noteID = req.parameters.get("id", as: UUID.self) else {
      throw Abort(.badRequest, reason: "无效的笔记ID")
    }

    guard
      let note = try await Note.query(on: req.db)
        .filter(\.$id == noteID)
        .filter(\.$user.$id == userID)
        .first()
    else {
      throw Abort(.notFound, reason: "笔记不存在或无权限")
    }

    try await note.delete(on: req.db)

    let audioID = note.$audio.id

    var keysToDelete = [RedisKey]()

    // 1. 添加音频相关缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.notesByAudio(audioID: audioID, userID: userID))

    // 2. 添加用户所有笔记的缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.notesByUser(userID: userID))

    // 3. 添加音频下所有用户笔记的缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.allNotesByAudio(audioID: audioID))

    // 4. 如果笔记有标签，添加标签相关缓存键
    if let tags = note.tags {
      for tag in tags {
        keysToDelete.append(CacheConfig.KeyBuilder.notesByTag(tag: tag, userID: userID))
      }
    }

    // 批量删除所有缓存
    if !keysToDelete.isEmpty {
      let _ = req.redis.delete(keysToDelete)
    }

    return BasicResponse(success: true, message: "笔记删除成功")
  }

  // 搜索笔记

  @Sendable
  func toggleNoteLike(req: Request) async throws -> BasicResponse {
    let currentUserID = try req.authenticatedUserID

    guard let noteID = req.parameters.get("id", as: UUID.self) else {
      throw Abort(.badRequest, reason: "无效的笔记ID")
    }
    guard let note = try await Note.find(noteID, on: req.db) else {
      throw Abort(.notFound, reason: "笔记不存在")
    }

    // 初始化 likedBy 数组（如果为空）
    if note.likedBy == nil {
      note.likedBy = []
    }

    // 检查是否已点赞
    let alreadyLiked = note.likedBy?.contains(currentUserID) ?? false

    if alreadyLiked {
      // 取消点赞
      note.likedBy?.removeAll(where: { $0 == currentUserID })
      note.likeCount = max(0, note.likeCount - 1)  // 确保不会小于0
    } else {
      // 添加点赞
      note.likedBy?.append(currentUserID)
      note.likeCount += 1
    }

    try await note.save(on: req.db)

    // 清除相关缓存
    let noteOwnerID = note.$user.id
    let audioID = note.$audio.id

    var keysToDelete = [RedisKey]()

    // 1. 添加笔记所有者的音频下笔记缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.notesByAudio(audioID: audioID, userID: noteOwnerID))

    // 2. 添加当前用户的音频下笔记缓存键（如果不是笔记所有者）
    if noteOwnerID != currentUserID {
      keysToDelete.append(
        CacheConfig.KeyBuilder.notesByAudio(audioID: audioID, userID: currentUserID))
    }

    // 3. 添加音频下所有用户笔记的缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.allNotesByAudio(audioID: audioID))

    // 4. 添加笔记所有者的所有笔记缓存键
    keysToDelete.append(CacheConfig.KeyBuilder.notesByUser(userID: noteOwnerID))

    // 5. 添加当前用户的所有笔记缓存键（如果不是笔记所有者）
    if noteOwnerID != currentUserID {
      keysToDelete.append(CacheConfig.KeyBuilder.notesByUser(userID: currentUserID))
    }

    // 6. 如果笔记有标签，清除标签相关缓存
    if let tags = note.tags, !tags.isEmpty {
      for tag in tags {
        keysToDelete.append(CacheConfig.KeyBuilder.notesByTag(tag: tag, userID: noteOwnerID))
      }
    }

    // 批量删除所有缓存
    if !keysToDelete.isEmpty {
      let _ = req.redis.delete(keysToDelete)
    }

    return BasicResponse(
      success: true,
      message: alreadyLiked ? "取消点赞成功" : "点赞成功"
    )
  }

  // 举报笔记
  @Sendable
  func reportNote(req: Request) async throws -> BasicResponse {
    let userID = try req.authenticatedUserID

    let reportRequest = try req.content.decode(Note.ReportNoteRequest.self)

    // 验证笔记是否存在
    guard try await Note.find(reportRequest.noteID, on: req.db) != nil else {
      throw Abort(.notFound, reason: "笔记不存在")
    }

    // 检查是否已经举报过
    let existingReport = try await Feedback.query(on: req.db)
      .filter(\.$reporter.$id == userID)
      .filter(\.$note.$id == reportRequest.noteID)
      .first()

    if existingReport != nil {
      throw Abort(.conflict, reason: "你已经举报过此笔记")
    }

    // 创建反馈记录
    let feedback = Feedback(
      reporterID: userID,
      category: reportRequest.category,
      type: reportRequest.type,
      content: reportRequest.content,
      status: .pending,
      noteID: reportRequest.noteID
    )

    try await feedback.save(on: req.db)

    return BasicResponse(success: true, message: "举报已提交，我们会尽快处理")
  }

  // 创建标签
  @Sendable
  func createNoteTag(req: Request) async throws -> NoteTag.Response {
    let userID = try req.authenticatedUserID
    let createRequest = try req.content.decode(NoteTag.Create.self)

    // 检查是否已存在同名标签
    let existingTag = try await NoteTag.query(on: req.db)
      .filter(\.$user.$id == userID)
      .filter(\.$name == createRequest.name)
      .first()

    if existingTag != nil {
      throw Abort(.conflict, reason: "已存在同名标签")
    }

    // 创建新标签
    let tag = NoteTag(name: createRequest.name, userID: userID)
    try await tag.save(on: req.db)

    // 清除标签列表缓存
    let cacheKey = CacheConfig.KeyBuilder.noteTagsByUser(userID: userID)
    try await req.cache.delete(forKey: cacheKey, on: req.redis)

    return NoteTag.Response(
      id: tag.id,
      name: tag.name,
      userID: userID,
      createdAt: tag.createdAt,
      updatedAt: tag.updatedAt,
      noteCount: 0
    )
  }

  // 获取我的所有标签
  @Sendable
  func fetchMyNoteTags(req: Request) async throws -> [NoteTag.Response] {
    let userID = try req.authenticatedUserID

    // 尝试从缓存获取
    let cacheKey = CacheConfig.KeyBuilder.noteTagsByUser(userID: userID)
    if let cachedTags = try await req.cache.get(
      forKey: cacheKey,
      as: [NoteTag.Response].self,
      from: req.redis
    ) {
      return cachedTags
    }

    // 查询数据库获取标签
    let tags = try await NoteTag.query(on: req.db)
      .filter(\.$user.$id == userID)
      .sort(\.$name, .ascending)
      .all()

    // 查询每个标签下的笔记数量
    var responses = [NoteTag.Response]()

    for tag in tags {
      // 查询包含此标签的笔记数量
      guard let sql = req.db as? SQLDatabase else {
        throw Abort(.internalServerError, reason: "数据库连接错误")
      }

      // 使用SQL查询包含特定标签的笔记数量
      let query = SQLQueryString(
        """
        SELECT COUNT(*) as count FROM \(unsafeRaw: Note.schema)
        WHERE user = \(bind: userID) AND JSON_CONTAINS(tags, \(bind: "\"" + tag.name + "\""))
        """)

      let rows = try await sql.raw(query).all()

      let noteCount = try rows.first?.decode(column: "count", as: Int.self) ?? 0

      responses.append(
        NoteTag.Response(
          id: tag.id,
          name: tag.name,
          userID: userID,
          createdAt: tag.createdAt,
          updatedAt: tag.updatedAt,
          noteCount: noteCount
        ))
    }

    // 缓存响应
    try await req.cache.set(
      responses,
      forKey: cacheKey,
      duration: CacheConfig.Duration.medium,
      type: .noteTag,
      on: req.redis
    )

    return responses
  }

  // 更新标签
  @Sendable
  func updateNoteTag(req: Request) async throws -> NoteTag.Response {
    let userID = try req.authenticatedUserID
    guard let tagID = req.parameters.get("id", as: UUID.self) else {
      throw Abort(.badRequest, reason: "无效的标签ID")
    }

    let updateRequest = try req.content.decode(NoteTag.Update.self)

    // 查找标签
    guard let tag = try await NoteTag.find(tagID, on: req.db) else {
      throw Abort(.notFound, reason: "标签不存在")
    }

    // 验证权限
    guard tag.$user.id == userID else {
      throw Abort(.forbidden, reason: "无权限修改此标签")
    }

    // 检查是否已存在同名标签
    let existingTag = try await NoteTag.query(on: req.db)
      .filter(\.$user.$id == userID)
      .filter(\.$name == updateRequest.name)
      .filter(\.$id != tagID)
      .first()

    if existingTag != nil {
      throw Abort(.conflict, reason: "已存在同名标签")
    }

    // 保存原始标签名用于更新笔记
    let oldTagName = tag.name

    // 更新标签
    tag.name = updateRequest.name
    tag.updatedAt = Date()
    try await tag.save(on: req.db)

    // 更新所有包含此标签的笔记
    guard let sql = req.db as? SQLDatabase else {
      throw Abort(.internalServerError, reason: "数据库连接错误")
    }

    // 查询包含此标签的所有笔记
    let notes = try await Note.query(on: req.db)
      .filter(\.$user.$id == userID)
      .all()

    // 在内存中更新标签
    for note in notes {
      if var tags = note.tags, tags.contains(oldTagName) {
        if let index = tags.firstIndex(of: oldTagName) {
          tags[index] = updateRequest.name
          note.tags = tags
          try await note.save(on: req.db)
        }
      }
    }

    // 清除相关缓存
    var keysToDelete = [RedisKey]()

    // 1. 清除标签列表缓存
    keysToDelete.append(CacheConfig.KeyBuilder.noteTagsByUser(userID: userID))

    // 2. 清除旧标签相关笔记缓存
    keysToDelete.append(CacheConfig.KeyBuilder.notesByTag(tag: oldTagName, userID: userID))

    // 3. 清除新标签相关笔记缓存
    keysToDelete.append(CacheConfig.KeyBuilder.notesByTag(tag: updateRequest.name, userID: userID))

    // 4. 清除用户所有笔记缓存
    keysToDelete.append(CacheConfig.KeyBuilder.notesByUser(userID: userID))

    // 批量删除缓存
    if !keysToDelete.isEmpty {
      let _ = req.redis.delete(keysToDelete)
    }

    // 查询标签下的笔记数量
    let countQuery = SQLQueryString(
      """
      SELECT COUNT(*) as count FROM \(unsafeRaw: Note.schema)
      WHERE user = \(bind: userID) AND JSON_CONTAINS(tags, \(bind: "\"" + updateRequest.name + "\""))
      """)

    let rows = try await sql.raw(countQuery).all()

    let noteCount = try rows.first?.decode(column: "count", as: Int.self) ?? 0

    return NoteTag.Response(
      id: tag.id,
      name: tag.name,
      userID: userID,
      createdAt: tag.createdAt,
      updatedAt: tag.updatedAt,
      noteCount: noteCount
    )
  }

  // 删除标签
  @Sendable
  func deleteNoteTag(req: Request) async throws -> BasicResponse {
    let userID = try req.authenticatedUserID
    guard let tagID = req.parameters.get("id", as: UUID.self) else {
      throw Abort(.badRequest, reason: "无效的标签ID")
    }

    // 获取删除选项
    let deleteRequest = try req.content.decode(NoteTag.Delete.self)

    // 查找标签
    guard let tag = try await NoteTag.find(tagID, on: req.db) else {
      throw Abort(.notFound, reason: "标签不存在")
    }

    // 验证权限
    guard tag.$user.id == userID else {
      throw Abort(.forbidden, reason: "无权限删除此标签")
    }

    // 获取标签名称用于后续处理
    let tagName = tag.name

    // 查询包含此标签的所有笔记
    let notes = try await Note.query(on: req.db)
      .filter(\.$user.$id == userID)
      .all()

    // 根据删除选项处理笔记
    if deleteRequest.deleteNotes {
      // 删除包含此标签的所有笔记
      for note in notes {
        if let tags = note.tags, tags.contains(tagName) {
          try await note.delete(on: req.db)
        }
      }
    } else {
      // 仅从笔记中移除标签引用
      for note in notes {
        if var tags = note.tags, tags.contains(tagName) {
          tags.removeAll { $0 == tagName }
          note.tags = tags
          try await note.save(on: req.db)
        }
      }
    }

    // 删除标签
    try await tag.delete(on: req.db)

    // 清除相关缓存
    var keysToDelete = [RedisKey]()

    // 1. 清除标签列表缓存
    keysToDelete.append(CacheConfig.KeyBuilder.noteTagsByUser(userID: userID))

    // 2. 清除标签相关笔记缓存
    keysToDelete.append(CacheConfig.KeyBuilder.notesByTag(tag: tagName, userID: userID))

    // 3. 清除用户所有笔记缓存
    keysToDelete.append(CacheConfig.KeyBuilder.notesByUser(userID: userID))

    // 4. 如果删除了笔记，清除相关音频的笔记缓存
    if deleteRequest.deleteNotes {
      // 这里可以添加更多缓存清理逻辑
      // 但由于我们不知道具体哪些音频受影响，可能需要更复杂的实现
    }

    // 批量删除缓存
    if !keysToDelete.isEmpty {
      let _ = req.redis.delete(keysToDelete)
    }

    return BasicResponse(
      success: true,
      message: deleteRequest.deleteNotes
        ? "标签及相关笔记已删除"
        : "标签已删除，相关笔记已更新"
    )
  }
}
